package com.kehongborui.staff.dao.impl;

import com.kehongborui.staff.dao.IStaffDao;
import com.kehongborui.staff.entity.Staff;
import com.kehongborui.staff.model.Condition;
import com.kehongborui.staff.model.Page;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description:   类描述
 * @Author:         XCQ
 * @email:          mr_xcq@163.com
 * @CreateDate:    2018-11-29 14:45
 * @UpdateUser:    username
 * @UpdateDate:    xxxx-xx-xx
 * @UpdateRemark:  修改内容
 * @Version:         1.0
*/
public class StaffDao implements IStaffDao {
    @Override
    public String create(Staff staff) {
        String result = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3308/staff?useSSL=false&serverTimezone=Asia/Shanghai","root","wwwm1905com");
            String sql = "INSERT INTO staff (staff_num,staff_name,staff_department,staff_gender,staff_age,staff_weight,staff_height,joined_time," +
                    "create_time) VALUE  (?,?,?,?,?,?,?,?,?)";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,staff.getStaffNum());
            statement.setString(2,staff.getStaffName());
            statement.setString(3,staff.getStaffDepartment());
            statement.setString(4,staff.getStaffGender());
            statement.setInt(5,staff.getStaffAge());
            statement.setDouble(6,staff.getStaffWeight());
            statement.setDouble(7,staff.getStaffHeight());
            statement.setDate(8,staff.getJoinedTime());
            statement.setDate(9,staff.getCreateTime());
            int flag = statement.executeUpdate();
            if (flag == 1) {
                result = "创建成功";
            } else {
                result = "创建失败";
            }
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public String updateById(Staff staff) {
        String result = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3308/staff?useSSL=false&serverTimezone=Asia/Shanghai","root","wwwm1905com");
            String sql = "UPDATE staff SET staff_num = ?,staff_name=?,staff_department=?,staff_gender=?,staff_age=?,staff_weight=?,staff_height=?," +
                    "joined_time=?,create_time=? WHERE id=?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,staff.getStaffNum());
            statement.setString(2,staff.getStaffName());
            statement.setString(3,staff.getStaffDepartment());
            statement.setString(4,staff.getStaffGender());
            statement.setInt(5,staff.getStaffAge());
            statement.setDouble(6,staff.getStaffWeight());
            statement.setDouble(7,staff.getStaffHeight());
            statement.setDate(8,staff.getJoinedTime());
            statement.setDate(9,staff.getCreateTime());
            statement.setInt(10,staff.getId());
            int flag = statement.executeUpdate();
            if (flag == 1) {
                result = "更新成功";
            } else {
                result = "更新失败";
            }
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public String deleteById(int id) {
        String result = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3308/staff?useSSL=false&serverTimezone=Asia/Shanghai","root","wwwm1905com");
            String sql = "DELETE FROM staff WHERE id=?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,id);
            int flag = statement.executeUpdate();
            if (flag == 1) {
                result = "删除成功";
            } else {
                result = "删除失败";
            }
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }


    @Override
    public List<Staff> queryAll(Condition condition, Page page) {
        List<Staff> staffs = new ArrayList<>();
        Staff staff;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3308/staff?useSSL=false&serverTimezone=Asia/Shanghai","root","wwwm1905com");
            String sql = "SELECT * FROM staff WHERE 1=1";
            if (condition.getStaffName() != null && !condition.getStaffName().equals("")) {
                sql += " AND staff_name LIKE '%"+ condition.getStaffName()+"%'";
            }
            if (condition.getStaffNum() !=0 ) {
                sql += " AND staff_num LIKE '%"+ condition.getStaffNum()+"%'";
            }
            if (condition.getStaffDepartment() != null && !condition.getStaffDepartment().equals("")) {
                sql += " AND staff_department LIKE '%"+ condition.getStaffDepartment()+"%'";
            }
            if (condition.getJoinedTime() != null && !condition.getJoinedTime().equals("")) {
                sql += " AND joined_time LIKE '%"+ condition.getJoinedTime()+"%'";
            }
            sql += " LIMIT ?,?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,page.getStartIndex());
            statement.setInt(2,page.getPageSize());
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                staff  = new Staff();
                staff.setId(resultSet.getInt("id"));
                staff.setStaffNum(resultSet.getInt("staff_num"));
                staff.setStaffName(resultSet.getString("staff_name"));
                staff.setStaffDepartment(resultSet.getString("staff_department"));
                staff.setStaffGender(resultSet.getString("staff_gender"));
                staff.setStaffAge(resultSet.getInt("staff_age"));
                staff.setStaffHeight(resultSet.getDouble("staff_height"));
                staff.setStaffWeight(resultSet.getDouble("staff_weight"));
                staff.setJoinedTime(resultSet.getDate("joined_time"));
                staffs.add(staff);
            }
            resultSet.close();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return staffs;
    }

    @Override
    public int queryNum(Condition condition) {
        int totalNum = 0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3308/staff?useSSL=false&serverTimezone=Asia/Shanghai","root","wwwm1905com");
            String sql = "SELECT * FROM staff WHERE 1=1";
            if (condition.getStaffName() != null && !condition.getStaffName().equals("")) {
                sql += " AND staff_name LIKE '%"+ condition.getStaffName()+"%'";
            }
            if (condition.getStaffNum() !=0 ) {
                sql += " AND staff_num LIKE '%"+ condition.getStaffNum()+"%'";
            }
            if (condition.getStaffDepartment() != null && !condition.getStaffDepartment().equals("")) {
                sql += " AND staff_department LIKE '%"+ condition.getStaffDepartment()+"%'";
            }
            if (condition.getJoinedTime() != null && !condition.getJoinedTime().equals("")) {
                sql += " AND joined_time LIKE '%"+ condition.getJoinedTime()+"%'";
            }
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                totalNum++;
            }
            resultSet.close();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return totalNum;
    }

    @Override
    public Staff queryById(int id) {
        Staff staff = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3308/staff?useSSL=false&serverTimezone=Asia/Shanghai","root","wwwm1905com");
            String sql = "SELECT * FROM staff WHERE id=?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,id);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                staff  =  new Staff();
                staff.setId(resultSet.getInt("id"));
                staff.setStaffNum(resultSet.getInt("staff_num"));
                staff.setStaffName(resultSet.getString("staff_name"));
                staff.setStaffDepartment(resultSet.getString("staff_department"));
                staff.setStaffGender(resultSet.getString("staff_gender"));
                staff.setStaffAge(resultSet.getInt("staff_age"));
                staff.setStaffHeight(resultSet.getDouble("staff_height"));
                staff.setStaffWeight(resultSet.getDouble("staff_weight"));
                staff.setJoinedTime(resultSet.getDate("joined_time"));
            }
            resultSet.close();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return staff;
    }
}
